# -*- coding:utf-8 -*-
"""
Author：Administrator
Date:2021年12月24日
"""
import pymysql
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from pandas.plotting import register_matplotlib_converters

register_matplotlib_converters() # 解决图表显示日期出现警告信息

conn = pymysql.connect(host='localhost', user='root', passwd='123456', db='test', charset='utf8')
sql_query = 'select * from test.user'
data = pd.read_sql(sql_query, con=conn)
conn.close()
data = data[['username', 'addtime']]
data.rename(columns={'addtime': '注册日期', 'username': '用户数量'}, inplace=True)
data['注册日期'] = pd.to_datetime(data['注册日期'])
data = data.set_index('注册日期')
data=data['2018-04-01':'2018-04-30']
# 按天统计新注册用户
df = data.resample('D').size().to_period('D')
df.to_excel('result1.xlsx',index=False)
x = pd.date_range(start='20180401',periods=30)
y = df
# 绘制折线图
sns.set_style('darkgrid')
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.title('新用户注册时间分布图')
plt.xticks(fontproperties = 'Times New Roman',size = 8,rotation=20)
plt.plot(x,y)
plt.xlabel('注册日期')
plt.ylabel('用户数量')
plt.show()
# print(x)